/*
 * 版权所有 (C) 2015 知启蒙(ZHIQIM) 保留所有权利。[遇见知启蒙，邂逅框架梦]
 * 
 * https://zhiqim.org/project/zhiqim_framework/zhiqim_orm.htm
 *
 * Zhiqim ORM is licensed under Mulan PSL v2.
 * You can use this software according to the terms and conditions of the Mulan PSL v2.
 * You may obtain a copy of Mulan PSL v2 at:
 *          http://license.coscl.org.cn/MulanPSL2
 * THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
 * EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
 * MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
 * See the Mulan PSL v2 for more details.
 */
package org.zhiqim.orm.impl;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.zhiqim.kernel.service.MultiInstancer;
import org.zhiqim.kernel.model.maps.HashMapCV;
import org.zhiqim.kernel.model.maps.LinkedMapSO;
import org.zhiqim.kernel.model.maps.MapSS;
import org.zhiqim.kernel.transaction.TransactionManager;
import org.zhiqim.kernel.util.Closes;
import org.zhiqim.kernel.util.Sqls;
import org.zhiqim.kernel.util.Validates;
import org.zhiqim.orm.ORMConstants;
import org.zhiqim.orm.ORMException;
import org.zhiqim.orm.ORMServer;
import org.zhiqim.orm.ZSQL;
import org.zhiqim.orm.annotation.AnDbo;
import org.zhiqim.orm.annotation.AnDboField;
import org.zhiqim.orm.dbo.CallParam;
import org.zhiqim.orm.dbo.CallResult;
import org.zhiqim.orm.dbo.defined._Dbo;
import org.zhiqim.orm.executor.BatchExecutor;
import org.zhiqim.orm.executor.CallExecutor;
import org.zhiqim.orm.executor.QueryExecutor;
import org.zhiqim.orm.executor.UpdateExecutor;

/**
 * 实现标准SQL调用，支持(executeUpdate, executeBatch, executeQuery)<br><br>
 * executeUpdate,       执行更新SQL语句，支持insert,replace,update,delete等<br>
 * executeBatch,        批量执行更新SQL语句，支持insert,replace,update,delete等<br>
 * executeQuery,        执行查询SQL语句，<br>
 *                      返回结果可以是boolean,byte,int,long,String,Timestamp,Date,Time,FClob,FBlob等10种类型<br>
 *                      
 * @version v1.0.0 @author zouzhigang 2014-3-21 新建与整理
 */
public final class ZSQLImplement extends MultiInstancer implements ZSQL, ORMConstants
{
    private ORMServer server;
    private HashMapCV<_Dbo> dboMap;
    
    public ZSQLImplement(ORMServer server)
    {
        this.server = server;
        this.dboMap = new HashMapCV<>();
    }
    
    /**
     * 获取ORM服务器
     * 
     * @return              数据库服务器
     */
    public ORMServer getServer()
    {
        return server;
    }
    
    /**
     * 通过数据库对象类获取数据库对象对象
     * 
     * @param clazz     数据库对象类
     * @return          数据库对象
     */
    public _Dbo getDbo(Class<?> clazz)
    {
        _Dbo _dbo = dboMap.get(clazz);
        if (_dbo != null)
            return _dbo;
        
        AnDbo result = clazz.getAnnotation(AnDbo.class);
        if (result == null)
            return null;
        
        _dbo = new _Dbo(clazz.getName());
        //增加字段
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields)
        {
            String fieldName = field.getName();
            AnDboField f = field.getAnnotation(AnDboField.class);
            if (f != null)
            {
                _dbo.addField(fieldName, f.column(), f.type());
            }
        }
        
        dboMap.put(clazz, _dbo);
        return _dbo;
    }
    
    /*****************************************************/
    //select @@IDENTITY as id 查自增ID
    /*****************************************************/
    
    /**
     * 查询当前自增ID值
     * 
     * @return              整型
     * @throws SQLException 数据库异常
     */
    public int identityInt() throws SQLException
    {
        return (int)identityLong();
    }
    
    /**
     * 查询当前自增ID值
     * 
     * @return              长整型
     * @throws SQLException 数据库异常
     */
    public long identityLong() throws SQLException
    {
        //检查是否有事务连接
        boolean isTx = true;
        Connection conn = (Connection)TransactionManager.getInstance(server.getId());
        if (conn == null)
        {//判断当前线程中有没有事务连接，如果没有则定义为非事务，有则为事务
            isTx = false;
            conn = server.getConnection();
        }
        
        PreparedStatement pstmt = null;
        ResultSet rst = null;

        try
        {
            pstmt = conn.prepareStatement(T_SELECT_IDENTITY);
            rst = pstmt.executeQuery();
            if (!rst.next())
                return -1;
            
            return rst.getLong(1);
        }
        finally
        {
            Closes.closeIgnoreException(rst, pstmt);
            if (!isTx)
            {//非事务时，要求归还连接
                conn.close();
            }
        }
    }
    
    /*****************************************************/
    //execute
    /*****************************************************/
    
    /**
     * 执行标准SQL语句，不带参数，如DDL,DML语句
     * 
     * @param sql           SQL语句 
     * @throws SQLException 数据库异常
     */
    public void execute(String sql) throws SQLException
    {
        if (Validates.isEmptyBlank(sql))
            return;
        
        if (server.isSqlite())
        {//SQLite作truncate处理
            sql = Sqls.formatSpace(sql);
            sql = sql.replaceAll("truncate table", "delete from");
        }
        
        //检查是否有事务连接
        boolean isTx = true;
        Connection conn = (Connection)TransactionManager.getInstance(server.getId());
        if (conn == null)
        {//判断当前线程中有没有事务连接，如果没有则定义为非事务，有则为事务
            isTx = false;
            conn = server.getConnection();
        }
        
        Statement stmt = null;

        try
        {
            stmt = conn.createStatement();
            stmt.execute(sql);
        }
        finally
        {
            Closes.closeIgnoreException(stmt);
            if (!isTx)
            {//非事务时，要求归还连接
                conn.close();
            }
        }
    }
    
    /**
     * 批执行标准SQL语句，不带参数，如DDL,DML语句
     * 
     * @param sqlList       SQL语句列表
     * @throws SQLException 数据库异常
     */
    public void execute(List<String> sqlList) throws SQLException
    {
        if (Validates.isEmpty(sqlList))
            return;
        
        boolean isTx = true;
        Connection conn = (Connection)TransactionManager.getInstance(server.getId());
        if (conn == null)
        {//如果没有连接则取默认非事务连接
            isTx = false;
            conn = server.getConnection();
        }
        
        Statement stmt = null;
        boolean autoCommit = true;
        try
        {
            autoCommit = conn.getAutoCommit();
            if (!isTx)
                conn.setAutoCommit(false);
            
            stmt = conn.createStatement();
            for (String sql : sqlList)
            {
                if (server.isSqlite())
                {//SQLite作truncate处理
                    sql = Sqls.formatSpace(sql);
                    sql = sql.replaceAll("truncate table", "delete from");
                }
                
                stmt.execute(sql);
            }
            
            if (!isTx)
                conn.commit();
        }
        catch (SQLException e) 
        {
            if (!isTx)
                conn.rollback();
            
            throw e;
        }
        finally
        {
            Closes.closeIgnoreException(stmt);
            if (!isTx)
            {//非事务时，要求归还连接，并设置回原来的自动提交值
                conn.setAutoCommit(autoCommit);
                conn.close();
            }
        }
    }
    
    /*****************************************************/
    //executeUpdate
    /*****************************************************/
    
    /**
     * 无参数executeUpdate执行
     * 
     * @param id                SQL配置编号
     * @return int              执行条数
     * @throws ORMException     映射异常
     * @throws SQLException     数据库异常
     */
    public int executeUpdateId(String id) throws ORMException, SQLException
    {
        return executeUpdate(id, null, null);
    }
    
    /**
     * executeUpdate执行
     * 
     * @param id                SQL配置编号
     * @param param             参数,需和executeUpdate配置对应，否则将报错
     * @return int              执行条数
     * @throws ORMException     映射异常
     * @throws SQLException     数据库异常
     */
    public int executeUpdateId(String id, Object param) throws ORMException, SQLException
    {
        return executeUpdateId(id, param, null);
    }
    
    /**
     * executeUpdate执行
     * 
     * @param id                SQL配置编号
     * @param param             参数,需和executeUpdate配置对应，否则将报错
     * @param replaceMap        字符替换表
     * @return int              执行条数
     * @throws ORMException     映射异常
     * @throws SQLException     数据库异常
     */
    public int executeUpdateId(String id, Object param, MapSS replaceMap) throws ORMException, SQLException
    {
        String sql = server.getSQL(id);
        if (sql == null)
            throw new ORMException("ZSQL[executeUpdateId]["+id+"]未找到配置");
        
        return executeUpdate(sql, param, replaceMap);
    }
    
    /**
     * executeUpdate执行
     * 
     * @param sql           SQL语句
     * @throws ORMException 映射异常
     * @throws SQLException 数据库异常
     */
    public int executeUpdate(String sql) throws ORMException, SQLException
    {
        return executeUpdate(sql, null, null);
    }
    
    /**
     * executeUpdate执行
     * 
     * @param sql           SQL语句
     * @param param         参数,需和update配置对应，否则将报错
     * @return int          执行条数
     * @throws ORMException 映射异常
     * @throws SQLException 数据库异常
     */
    public int executeUpdate(String sql, Object param) throws ORMException, SQLException
    {
        return executeUpdate(sql, param, null);
    }
    
    /**
     * executeUpdate执行
     * 
     * @param sql           SQL语句
     * @param param         参数,需和update配置对应，否则将报错
     * @param replaceMap    字符替换表
     * @return int          执行条数
     * @throws ORMException 映射异常
     * @throws SQLException 数据库异常
     */
    public int executeUpdate(String sql, Object param, MapSS replaceMap) throws ORMException, SQLException
    {
        //判断update和传参是事匹配
        UpdateExecutor updateExecutor = server.getUpdateExecutor(param);
        if (updateExecutor == null)
            throw new ORMException("ZSQL[executeUpdate]不支持的参数类型");
        
        //检查是否有事务连接
        boolean isTx = true;
        Connection conn = (Connection)TransactionManager.getInstance(server.getId());
        if (conn == null)
        {//判断当前线程中有没有事务连接，如果没有则定义为非事务，有则为事务
            isTx = false;
            conn = server.getConnection();
        }

        try
        {
            return updateExecutor.execute(server, conn, sql, param, replaceMap);
        }
        finally
        {
            if (!isTx)
            {//非事务时，要求归还连接
                conn.close();
            }
        }
    }
    
    /*****************************************************/
    //executeBatch
    /*****************************************************/
    
    /**
     * executeBatch执行
     * 
     * @param id            SQL配置编号
     * @param paramList     参数列表
     * @return int[]        执行结果数组
     * @throws ORMException 映射异常
     * @throws SQLException 数据库异常
     */
    public int[] executeBatchId(String id, List<?> paramList) throws ORMException, SQLException
    {
        return executeBatchId(id, paramList, null);
    }
    
    /**
     * executeBatch执行
     * 
     * @param id            SQL配置编号
     * @param paramList     参数列表
     * @param replaceMap    字符替换表
     * @return int[]        执行结果数组
     * @throws ORMException 映射异常
     * @throws SQLException 数据库异常
     */
    public int[] executeBatchId(String id, List<?> paramList, MapSS replaceMap) throws ORMException, SQLException
    {
        String sql = server.getSQL(id);
        if (sql == null)
            throw new ORMException("ZSQL[executeBatchId]["+id+"]未找到配置");
        
        return executeBatch(sql, paramList, replaceMap);
    }
    
    /**
     * executeBatch执行
     * 
     * @param sql           SQL语句
     * @param paramList     参数列表
     * @return              int[] 执行结果数组
     * @throws ORMException 映射异常
     * @throws SQLException 数据库异常
     */
    public int[] executeBatch(String sql, List<?> paramList) throws ORMException, SQLException
    {
        return executeBatch(sql, paramList, null);
    }
    
    /**
     * executeBatch执行
     * 
     * @param sql           SQL语句
     * @param paramList     参数列表
     * @param replaceMap    字符替换表
     * @return int[]        执行结果数组
     * @throws ORMException 映射异常
     * @throws SQLException 数据库异常
     */
    public int[] executeBatch(String sql, List<?> paramList, MapSS replaceMap) throws ORMException, SQLException
    {
        //批处理为空
        if (paramList == null || paramList.isEmpty())
            return new int[0];
        
        //判断update和传参是事匹配
        BatchExecutor batchExecutor = server.getBatchExecutor(paramList.get(0));
        if (batchExecutor == null)
            throw new ORMException("ZSQL[executeBatch]不支持的参数类型");

        boolean isTx = true;
        Connection conn = (Connection)TransactionManager.getInstance(server.getId());
        if (conn == null)
        {//如果没有连接则取默认非事务连接
            isTx = false;
            conn = server.getConnection();
        }
        
        boolean autoCommit = true;
        try
        {
            autoCommit = conn.getAutoCommit();
            if (!isTx)
                conn.setAutoCommit(false);
            
            int[] result = batchExecutor.execute(server, conn, sql, paramList, replaceMap);
            if (!isTx)
                conn.commit();
            
            return result;
        }
        catch (SQLException | ORMException e) 
        {
            if (!isTx)
                conn.rollback();
            
            throw e;
        }
        finally
        {
            if (!isTx)
            {//非事务时，要求归还连接，并设置回原来的自动提交参数
                conn.setAutoCommit(autoCommit);
                conn.close();
            }
        }
    }
    
    /*****************************************************/
    //executeQuery
    /*****************************************************/
    
    /**
     * executeQuery执行
     * 
     * @param id            SQL配置编号
     * @param resultClass   结果类结构
     * @return              结果集
     * @throws ORMException 映射异常
     * @throws SQLException 数据库异常
     */
    public <T> List<T> executeQueryId(String id, Class<T> resultClass) throws ORMException, SQLException
    {
        return executeQueryId(id, resultClass, null, null);
    }
    
    /**
     * executeQuery执行
     * 
     * @param id            SQL配置编号
     * @param resultClass   结果类结构
     * @param param         参数
     * @return              结果集
     * @throws ORMException 映射异常
     * @throws SQLException 数据库异常
     */
    public <T> List<T> executeQueryId(String id, Class<T> resultClass, Object param) throws ORMException, SQLException
    {
        return executeQueryId(id, resultClass, param, null);
    }
    
    /**
     * executeQuery执行
     * 
     * @param id            SQL配置编号
     * @param resultClass   结果类结构
     * @param param         参数
     * @param replaceMap    字符替换表
     * @return              结果集
     * @throws ORMException 映射异常
     * @throws SQLException 数据库异常
     */
    public <T> List<T> executeQueryId(String id, Class<T> resultClass, Object param, MapSS replaceMap) throws ORMException, SQLException
    {
        String sql = server.getSQL(id);
        if (sql == null)
            throw new ORMException("ZSQL[executeQueryId]["+id+"]未找到配置");
        
        return executeQuery(sql, resultClass, param, replaceMap);
    }
    
    /**
     * executeQuery执行
     * 
     * @param sql           SQL语句
     * @return              结果集
     * @throws ORMException 映射异常
     * @throws SQLException 数据库异常
     */
    public List<LinkedMapSO> executeQuery(String sql) throws ORMException, SQLException
    {
        return executeQuery(sql, LinkedMapSO.class);
    }
    
    /**
     * executeQuery执行
     * 
     * @param sql           SQL语句
     * @param resultClass   结果类结构
     * @return              结果集
     * @throws ORMException 映射异常
     * @throws SQLException 数据库异常
     */
    public <T> List<T> executeQuery(String sql, Class<T> resultClass) throws ORMException, SQLException
    {
        return executeQuery(sql, resultClass, null, null);
    }
    
    /**
     * executeQuery执行
     * 
     * @param sql           SQL语句
     * @param resultClass   结果类结构
     * @param param         参数
     * @return              结果集
     * @throws ORMException 映射异常
     * @throws SQLException 数据库异常
     */
    public <T> List<T> executeQuery(String sql, Class<T> resultClass, Object param) throws ORMException, SQLException
    {
        return executeQuery(sql, resultClass, param, null);
    }
    
    /**
     * executeQuery执行
     * 
     * @param sql           SQL语句
     * @param resultClass   结果类结构
     * @param param         参数
     * @param replaceMap    字符替换表
     * @return              结果集
     * @throws ORMException 映射异常
     * @throws SQLException 数据库异常
     */
    @SuppressWarnings("unchecked")
    public <T> List<T> executeQuery(String sql, Class<T> resultClass, Object param, MapSS replaceMap) throws ORMException, SQLException
    {
        //判断query和传参是事匹配
        QueryExecutor queryExecutor = server.getQueryExecutor(param, resultClass);
        if (queryExecutor == null)
            throw new ORMException("ZSQL[executeQuery]不支持的参数或结果类型");
        
        //检查是否有事务连接
        boolean isTx = true;
        Connection conn = (Connection)TransactionManager.getInstance(server.getId());
        if (conn == null)
        {//判断当前线程中有没有事务连接，如果没有则定义为非事务，有则为事务
            isTx = false;
            conn = server.getConnection();
        }

        try
        {
            return (List<T>) queryExecutor.execute(server, conn, sql, resultClass, param, replaceMap);
        }
        finally
        {
            if (!isTx)
            {//非事务时，要求归还连接
                conn.close();
            }
        }
    }
    
    /************************************************************************************************/
    //call (函数&存储过程调用)
    /************************************************************************************************/
    
    /**
     * call 函数&存储过程执行，无结果集，一般用于函数或count/sum等简单的存储过程
     * 
     * @param sql           SQL语句
     * @param paramList     参数列表
     * @throws ORMException 映射异常
     * @throws SQLException 数据库异常
     */
    public void call(String sql, List<CallParam> paramList) throws ORMException, SQLException
    {
        call(sql, paramList, null, null);
    }
    
    
    /**
     * call 函数&存储过程执行，支持替换表，无结果集，一般用于函数或count/sum等简单的存储过程
     * 
     * @param sql           SQL语句
     * @param paramList     参数列表
     * @param replaceMap    字符替换表
     * @throws ORMException 映射异常
     * @throws SQLException 数据库异常
     */
    public void call(String sql, List<CallParam> paramList, MapSS replaceMap) throws ORMException, SQLException
    {
        call(sql, paramList, null, replaceMap);
    }
    
    /**
     * call 存储过程执行，返回一个结果集
     * 
     * @param sql           SQL语句
     * @param paramList     参数列表
     * @param replaceMap    字符替换表
     * @throws ORMException 映射异常
     * @throws SQLException 数据库异常
     */
    @SuppressWarnings("unchecked")
    public <T> List<T> call(String sql, List<CallParam> paramList, Class<T> resultClass) throws ORMException, SQLException
    {
        List<CallResult> resultList = new ArrayList<>(1);
        resultList.add(CallResult.set(resultClass));
        
        call(sql, paramList, resultList, null);
        return (List<T>)resultList.get(0).getResultList();
    }
    
    /**
     * call 函数&存储过程执行，支持多结果集
     * 
     * @param sql           SQL语句
     * @param paramList     参数列表
     * @param resultList    结果集列表
     * @throws ORMException 映射异常
     * @throws SQLException 数据库异常
     */
    public void call(String sql, List<CallParam> paramList, List<CallResult> resultList) throws ORMException, SQLException
    {
        call(sql, paramList, resultList, null);
    }
    
    /**
     * call 函数&存储过程执行，支持多结果集
     * 
     * @param sql           SQL语句
     * @param paramList     参数列表
     * @param resultList    结果集列表
     * @param replaceMap    字符替换表
     * @throws ORMException 映射异常
     * @throws SQLException 数据库异常
     */
    public void call(String sql, List<CallParam> paramList, List<CallResult> resultList, MapSS replaceMap) throws ORMException, SQLException
    {
        //检查是否有事务连接
        boolean isTx = true;
        Connection conn = (Connection)TransactionManager.getInstance(server.getId());
        if (conn == null)
        {//判断当前线程中有没有事务连接，如果没有则定义为非事务，有则为事务
            isTx = false;
            conn = server.getConnection();
        }

        try
        {
            CallExecutor executor = server.getCallExecutor();
            executor.execute(server, conn, sql, paramList, resultList, replaceMap);
        }
        finally
        {
            if (!isTx)
            {//非事务时，要求归还连接
                conn.close();
            }
        }
    }
    
    /*****************************************************/
    //exist 查询是否存在表或字段
    /*****************************************************/
    
    public boolean existTable(String table) throws ORMException, SQLException
    {
        String sql = server.getPolicy().toExistsSQL(server.getDatabaseName(), table);
        List<Integer> result = executeQuery(sql, Integer.class);
        return result.get(0) > 0;
    }
}
